<?php

class Territory_model extends CI_Model {

	private $idnya = 0;
	private $result = '';
	
	public function __construct()
	{
		$this->load->model('common_model');
	}
	
	public function get_territory_type()
	{
		$query = $this->db->get('mtr_territory_type');
		
		return $query->result_array();
	}
	
	function getTerritoryType($id)
	{
		$dblokal  = $this->load->database("default", TRUE);
		$dblokal->where("territory_type_id", $id);
		$query = $dblokal->get("t_mtr_territory_type");
		
		return $query->row_array();
	}
	
	function getTerritory($pid = 0, $type="", $limit=0)//$limit u/ menentukan ter_id mulai dr mana
	{    
		$dblokal  = $this->load->database("default", TRUE);
		$and = "";
		$order = 'territory_name ASC' ;
		  if ($type <>  ""){
			$and = 'and territory_type_id = '.$type.' and territory_id > '.$limit.' ' ;
		  }

		  $cast = "cast(SUBSTRING(territory_name, 'Y*([0-9]{1,3})' ) as INTEGER)";
		  $row = $this->getTerritoryPid($pid);
		  if ($pid <>""){
			$row = $this->getTerritoryPid($pid);
			if($row == 2){																		
			   $order = $cast;
			}else{
				$order = 'territory_name asc' ;
			}
		   }
		$sql = 'select * from t_mtr_territory where parent_id = '.$pid.'
				AND istatus = 1
				'.$and.' 
				ORDER BY '.$order.' limit 50' ;
		$query = $dblokal->query($sql);

		return $query;
	}
	
	function getTerritoryTree($id = 0, $tipe = '', $limit = 0)
    {
		//Get user group role 
		$user_group_id = $this->session->userdata('user_group_id');
		$row  = $this->getButtons(8, $user_group_id);
		//-----------------------------------------------------//

		$query = $this->getTerritory($id,$tipe,$limit);
		$k=0;
		$jenis = $this->showdata('territory_type_id','t_mtr_territory',array('territory_id'=>$id),'dblokal');
		if($jenis == 3 && $tipe == ''){
			$cek_o = $this->getTerritory($id,5)->num_rows();
			$cek_a = $this->getTerritory($id,6)->num_rows();
			
			$this->result .= '[{"id"	: "o'.$id.'",
					   "tipe"	: 5,
					   "ter_id" 	: '. $id .',
					   "limit"	: "",
					   "name"	: "Outlet",
					   "size"	: "", "date":"",
					   "iconCls"	: "icon-territory-outlet"';
			if($cek_o >0){
				$this->result .= ',"state":"closed"';
			}
			$this->result .='},';
			$this->result .= '{"id"		: "a'.$id.'",
					   "tipe"	: 6,
					   "ter_id"	: '. $id .',
					   "limit"	: "",
					   "name"	: "Agent / Location",
					   "size"	: "", "date":"",
					   "iconCls"	: "icon-territory-agent"';
			if($cek_a >0){
				$this->result .= ',"state":"closed"';
			}
			$this->result .='}]';
			
		}else{
			$jum = $query->num_rows();
			$this->idnya ++; $lm=''; $tp='';
			if($query->num_rows()>0){
				$this->result .='[';
				foreach($query->result() as $r){
					
					#cek punya children gak ?
					$cek = $this->getTerritory($r->territory_id);$k++;
					$jml = $cek->num_rows();
					
					$link="";
					if($r->territory_type_id==1){

						$link= ($row['menu_add'] == 1) ? "<a href='".base_url('/territory/input/add/'.$r->territory_id) ."'>Add</a>" : "";
					}
					elseif($r->territory_type_id == 2){
					
						$link = ($row['menu_add'] == 1)     ? "<a href='".base_url('/territory/input/add/'.$r->territory_id)."'>Add</a> | " : "";
						$link .= ($row['menu_edit'] == 1)   ? "  <a href='".base_url('/territory/input/edit/'.$r->territory_id)."'>Edit</a> | " :"";
						$link .= ($row['menu_delete'] == 1) ? "  <a href='#' onclick=deleterow('". $this->idnya ."','". $r->territory_id ."','". $jml ."') >Delete</a> | " : "";
					
						$links = explode('|', $link);
						unset($links[count($links) - 1]);
						$link = implode(' | ', $links);
					
					}
					elseif($r->territory_type_id == 3){
						$link .= ($row['menu_edit'] == 1)   ? "  <a href='".base_url('/territory/input/edit/'.$r->territory_id)."'>Edit</a> | " :"";
						$link .= ($row['menu_delete'] == 1) ? "  <a href='#' onclick=deleterow('". $this->idnya ."','". $r->territory_id ."','". $jml ."') >Delete</a>" : "";
					
						$links = explode('|', $link);
						unset($links[count($links) - 1]);
						$link = implode(' | ', $links);
					}
					
					$tpe = "";
					if($jenis>=3){
						$tpe = $r->territory_id ;
					}
					$this->result .='{"id"		: '. $r->territory_id .',
							"name"		: "'. $r->territory_name .'",
							"size"		: "'. $this->showdata('user_name','t_mtr_user',array('user_id'=>$r->user_id),'dblokal') .'",
							"date"		: "'. $link .'",
							"ter_id"	: '. $r->territory_id .',
							"tipe"		: "'. $tpe .'",
							"limit"		: "",
							"iconCls"	: "icon-territory-'. $r->territory_type_id .'"';
					if($cek->num_rows() >0){
						$this->result .= ',"state":"closed"';
					}
					$this->result .='}';
					if($k!=$jum) $this->result .=',';
					$lm = $r->territory_id;
					$tp = $r->territory_type_id;
				}
				
				if($jenis >= 3){
					$ada_data = $this->countData('territory_type_id','t_mtr_territory',array('parent_id'=>$id,'territory_id >'=>$limit,'territory_type_id'=>$tipe),'dblokal');
					if($ada_data > 50){
						$this->result .=',{"id"	: "showdata",
							"name"	: "<font color=red align=center onclick=javascript:append(\'showdata\','.$id.','. $tp .','. ($lm) .')><u>--- Show More Data ---</u></font>",
							"ter_id": '. $id .',
							"size"  : "",
							"date"  : "",
							"tipe"  : "",
							"iconCls": "icon-territory-more"}';
					}
				}
				$this->result .="]";
			}
		}
        
		return $this->result;
	}
	
	function getTerritoryPid($pid)
	{
		if ($pid <>"") {
			$dblokal  = $this->load->database("default", TRUE);
            	
			$r = $dblokal->query("select territory_type_id from t_mtr_territory where territory_id = ".$pid." ");
			return  $r->row()->territory_type_id;
		}
	}
	
	function getTerritoryById($id)
	{    
		$dblokal  = $this->load->database("default", TRUE);
		$dblokal->where("territory_id", $id);
		$query = $dblokal->get("t_mtr_territory");
		return $query->row_array();
	}
	
	function showData( $data, $tbl, $where, $db, $like = "" )
	{    
		$dblokal  = $this->load->database("default", TRUE);
		
		${$db}->where($where);
		if($like <> "") ${$db}->like($like);
		$query = ${$db}->get($tbl);
		$val=1;
		if($val == 0){
			return "";
		}else{
			if($query->num_rows() > 0) {
					$r = $query->row();
					return $r->{$data};
			} else {
					return "";
			}       
		}
	}
	
	function countData( $data, $tbl, $where, $db, $like = "" )
	{    
		$dblokal  = $this->load->database("default", TRUE);
		
		${$db}->where($where);
		if($like <> "") ${$db}->like($like);
		$query = ${$db}->get($tbl);
		
		return $query->num_rows();
	}
	
	public function getButtons($menu_id, $user_group_id)
	{
		$this->db->select("COALESCE (menu_add, 0) AS menu_add, COALESCE (menu_view, 0) AS menu_view, COALESCE (menu_edit, 0) AS menu_edit", false);
		$this->db->select("COALESCE (menu_delete, 0) AS menu_delete, COALESCE (menu_approve, 0) AS menu_approve, COALESCE (menu_reject, 0) AS menu_reject", false);
		$this->db->where("user_group_id", $user_group_id);
		$this->db->where("menu_id", $menu_id);
		$query = $this->db->get("t_mtr_user_role");

		if($query->num_rows() > 0) {
		  $result = $query->row_array();
		}
		else {
		  $result['menu_add']     = 0;
		  $result['menu_view']    = 0;
		  $result['menu_edit']    = 0;
		  $result['menu_delete']  = 0;
		  $result['menu_reject']  = 0;
		  $result['menu_approve'] = 0;
		}
		return  $result;
	}
	
	function getValueFromTable( $tbl, $where=array(), $db="dblokal" )
	{    
		$dblokal  = $this->load->database("default", TRUE);
		
		${$db}->where($where);
		$query = ${$db}->get($tbl);
		
		return $query->result_array();
	}
	
	function getValueFromTableIN( $tbl, $where=array(), $db="dblokal" )
	{
		$dblokal  = $this->load->database("default", TRUE);
		
		${$db}->where_in('user_group_id',$where);
		${$db}->order_by('user_name','ASC');
		$query = ${$db}->get($tbl);
		
		return $query->result_array();
	}
	
	function saveTerritory($proses, $id, $icon = '')
    {
        $dblokal = $this->load->database("default", TRUE); 
		$name 	 = $this->input->post("name");
		$parent  = $this->input->post("parent");
		$user 	 = $this->input->post("user");
		$territory_type = $this->input->post("territory_type");
		$territory_code = $this->input->post("territory_code");
		$territory_code2= $this->input->post("territory_code2");
		
		$data = array("territory_name" 	=>$name,
			      "parent_id" 	=>$parent,
			      'updated_by'=>$this->session->userdata('username'));
		if($user<>"") $data["user_id"]=$user;
		if($proses == "update"){
			
			//ambil repto lama & yg baru
			$rlama = $this->common_model->showdata("user_id","t_mtr_territory",array('territory_id'=>$id),"dblokal");
			$rbaru = $user;
			
			$data["territory_type_id"]=$territory_type;
			$dblokal->where(array("territory_id"=>$id));
			$dblokal->update("t_mtr_territory",$data);
			
			if($icon<>""){
				$dblokal->where(array("territory_type_id"=>$territory_type));
				$dblokal->update("t_mtr_territory_type",array("territory_type_icon"=>$icon));
			}
			
			$dblokal->where(array("territory_id"=>$id));
			$dblokal->update("t_mtr_territory_code",array("territory_code"=>$territory_code,
														  "territory_code_v2"=>$territory_code2,
														  "updated_by"=>$this->session->userdata('username'),
														  "updated_on"=>date('Y-m-d H:i:s')
														  )
							 );
			
			/*
				Kalo maintenance_by terr di-update,pengaruh ke user,
				user yang ada di bawahnya diupdate reportingto-nya
			*/
			if($rlama<>"" && $rlama<>$rbaru){
				$dblokal->where(array("reporting_to"=>$rlama));
				$dblokal->update("t_mtr_user",array("reporting_to"=>$rbaru));
			}
			$territory = $this->db->get("f_get_territory_path('$id')")->row()->f_get_territory_path;
			$this->common_model->saveLog('territory',"update ($territory)",$id);
		}else{
			$data["territory_type_id"]=$territory_type+1;
			$data['created_by']=$this->session->userdata('username');
			
			$dblokal->insert("t_mtr_territory",$data);
			$dblokal->insert("t_mtr_territory_code",array('territory_code'=>$territory_code,
								      'territory_id'=>$dblokal->insert_id(),
								      'istatus'=>1,
								      'created_by'=>$this->session->userdata('username')));
			$ref_id = $this->db->insert_id();
			$territory = $this->db->get("f_get_territory_path('$id')")->row()->f_get_territory_path;
			$this->common_model->saveLog('territory',"insert ($territory)",$ref_id);
			#update di t_mtr_user
			$report = $this->common_model->showdata("user_id","t_mtr_territory",array('territory_id'=>$parent),"dblokal");
			if($report<>""){
				$dblokal->where(array("user_id"=>$user));
				$dblokal->update("t_mtr_user",array("reporting_to"=>$report));
			}
		}
    }
}